package com.erp.framework.model.dao;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;

import com.erp.app.common.ProjectConstants;
import com.erp.framework.SimpleConstants;
import com.erp.framework.exceptions.CreateRecordException;
import com.erp.framework.exceptions.DeleteRecordException;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.exceptions.ModifyRecordException;
import com.erp.framework.exceptions.RecordModifiedException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.logger.Logger;
import com.erp.framework.util.user.User;

public abstract class SimpleBaseDao implements Serializable, Cloneable, SimpleConstants {

    public SimpleBaseDao() {

    }
    
    public Collection findByCriteria(SimpleBaseVo bizModel, Object obj, int fetch_size, int start_idx, Connection conn) throws FindRecordException {
        throw new UnsupportedOperationException();
    }
        
    public int findTotal(SimpleBaseVo model, SimpleBaseSrchCriteria crit, Connection conn) throws FindRecordException {
        throw new UnsupportedOperationException();
    }

    public abstract SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) throws FindRecordException;

    public void setMaxRows(Statement stmt) throws Exception{        
        stmt.setMaxRows(ProjectConstants.DB_ROW_NUM);
    }

    public abstract ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException;

    public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {

        throw new UnsupportedOperationException();
    }

    public ChildList selectByParent(SimpleBaseVo parent) throws FindRecordException {

        ChildList childs = null;
        Connection conn = null;
        try {
            conn = ServiceLocator.getInstance().getDSDBConnection();
            childs = selectByParent(parent, conn);
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            if (e instanceof FindRecordException) throw ((FindRecordException) e); else throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                Logger.logExceptions(this, e);
            }
        }
        return childs;
    }

    public final void selectChilds(SimpleBaseVo bizModel, Connection conn) {

        try {
            if (bizModel != null) {
                Class[] childTypes = bizModel.getChildTypes();
                if (childTypes != null && childTypes.length > 0) {
                    for (int i = 0; i < childTypes.length; i++) {
                        SimpleBaseVo tmpChild = (SimpleBaseVo) childTypes[i].newInstance();
                        SimpleBaseDao childDAO = (SimpleBaseDao) tmpChild.getDaoType().newInstance();
                        ChildList childList = childDAO.selectByParent(bizModel, conn);

                        if (childList != null && childList.size() > 0) {
                            bizModel.storeChilds(childList);

                            Iterator itChild = childList.iterator();
                            while (itChild.hasNext()) {
                                SimpleBaseVo childModel = (SimpleBaseVo) itChild.next();
                                childModel.setParent(bizModel);

                                if (childModel.getChildTypes() != null && childModel.getChildTypes().length > 0) {
                                    // /// recursive call
                                    selectChilds(childModel, conn);
                                }
                            }// end while
                        }
                    }// end for
                }// end if
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        }
    }// end selectChilds

    //
    // Insert
    //
    public abstract void insert(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException;

    public void insertModel(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {

        insertModel(bizModel, null, conn);
    }

    public void insertModel(SimpleBaseVo bizModel, User user, Connection conn) throws CreateRecordException {

        if (bizModel == null)
            return;

        try {
            // insert the root first
            if (!isViewOnly())
                insert(bizModel, conn);

            // for specific case such as: sequence-generated primary key for
            // parent and child
            beforeInsertChild(bizModel, conn);

            // insert the branch in recursive manner
            if (bizModel.getChildTypes() != null && bizModel.getChildTypes().length > 0) {
                for (int i = 0; i < bizModel.getChildTypes().length; i++) {
                    ChildList childLst = bizModel.getChilds(bizModel.getChildTypes()[i]);
                    if (childLst != null) {
                        Iterator itChild = childLst.iterator();
                        while (itChild.hasNext()) {
                            SimpleBaseVo childModel = (SimpleBaseVo) itChild.next();
                                                        
                            childModel.setParent(bizModel);
                            SimpleBaseDao childDAO = (SimpleBaseDao) childModel.getDaoType().newInstance();

                            if (childModel.getIsDelete()) {
                                if (childModel.getIsExisting()) {
                                    Logger.warn(this, "insertModel(), You shouldn't be deleting while inserting.");
                                    childDAO.removeModel(childModel, conn);
                                }
                                itChild.remove();
                            } else {
                                // insert the child here
                                childDAO.insertModel(childModel, conn);
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new CreateRecordException(e.getMessage());
        }
    }

    public void beforeInsertChild(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {

    }

    //
    // Update
    //
    public abstract void update(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException;

    public void updateModel(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException, RecordModifiedException {

        updateModel(bizModel, null, conn);
    }

    public void updateModel(SimpleBaseVo bizModel, User user, Connection conn) throws ModifyRecordException,
            RecordModifiedException {

        if (bizModel == null)
            return;
        
        if (!bizModel.getIsDirty()) {
        	Logger.debug("updateModel(), bizModel is not dirty");
            return;
        }

        try {
            // Check database record version
            if (bizModel.getIsExisting() && !isViewOnly()) {
                SimpleBaseVo dbImage = findByPrimaryKey(bizModel.getPrimaryKey(), conn);
                Logger.trace(this, "updateModel(), Got dbImage = " + dbImage + ", pk = " + bizModel.getPrimaryKey());

                if (CHECK_VERSION) {
                    if (dbImage == null || !bizModel.isSameVersion(dbImage)) {
                        Logger.trace(this, "updateModel(), They are different *** !!!! (db = " + dbImage + ", pk = "
                                + bizModel.getPrimaryKey() + ")");
                        throw new RecordModifiedException(
                                "Record may have been modified by another user! Please click on the 'Refresh' button to load the latest data before saving!");
                    }
                }

            }
            // update the root first
            if (!isViewOnly())
                update(bizModel, conn);

            // update the branches in recursive manner
            Class[] childTypes = bizModel.getChildTypes();

            for (int i = 0; childTypes != null && i < childTypes.length; i++) {
                ChildList childLst = bizModel.getChilds(childTypes[i]);

                if (childLst != null) {
                    Iterator itChild = childLst.iterator();
                    while (itChild.hasNext()) {
                        SimpleBaseVo childModel = (SimpleBaseVo) itChild.next();
                        childModel.setParent(bizModel);
                        SimpleBaseDao childDAO = (SimpleBaseDao) childModel.getDaoType().newInstance();
                        Logger.debug(this, "updateModel(), child pk = " + childModel.getPrimaryKey()
                        		+ ", child vo class is: " + childModel.getClass().getName()
                        		+ ", exist = "
                                + childModel.getIsExisting() + ", delete = " + childModel.getIsDelete()
                                + ", isDirty = " + childModel.getIsDirty());
                        if (childModel.getIsExisting()) {

                            if (!childModel.getIsDelete()) {
                                // Case 1. updates an existing record
                                childDAO.updateModel(childModel, conn);
                            } else {
                                // Case 2. deletes the whole branch
                                childDAO.removeModel(childModel, conn);
                                itChild.remove();
                            }
                        } else {
                            if (!childModel.getIsDelete()) {
                                // Case 3. inserts an existing record
                                childDAO.insertModel(childModel, conn);
                            } else
                                itChild.remove();
                        }
                    } // _iterate_ over each child on ChildList
                }
            }
        } // _for_ each ChildType
        catch (RecordModifiedException rme) {
            throw rme;
        } catch (Exception e1) {
            throw new ModifyRecordException(e1.getMessage());
        }
    }

    //
    // Delete
    //
    public abstract void remove(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException;

    public void removeModel(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException {

        removeModel(bizModel, null, conn);
    }

    public void removeModel(SimpleBaseVo bizModel, User user, Connection conn) throws DeleteRecordException {

        if (bizModel == null)
            return;

        try {
            // Check database record version
            if (bizModel.getIsExisting() && !isViewOnly()) {
                SimpleBaseVo dbImage = findByPrimaryKey(bizModel.getPrimaryKey(), conn);
                if (dbImage == null || !bizModel.isSameVersion(dbImage)) {

                    Logger.trace(this, "removeModel(), dbImages are different *** !!!!");

                }
            }
                   
            ChildList tmpChild = null;
            // remove the branches in recursive manner
            if (bizModel.getChildTypes() != null && bizModel.getChildTypes().length > 0) {
                for (int i = 0; i < bizModel.getChildTypes().length; i++) {
                    ChildList childLst = bizModel.getChilds(bizModel.getChildTypes()[i]);
                    tmpChild = childLst;
                    beforeRemoveChild(bizModel, tmpChild, bizModel.getChildTypes()[i], conn);
                    if (childLst != null) {
                        Iterator itChild = childLst.iterator();
                        while (itChild.hasNext()) {
                            SimpleBaseVo childModel = (SimpleBaseVo) itChild.next();
                            childModel.setDeleteForever(bizModel.isDeleteForever());
                            childModel.setParent(bizModel);
                            childModel.setIsDelete(true);
                            SimpleBaseDao childDAO = (SimpleBaseDao) childModel.getDaoType().newInstance();
                            childDAO.removeModel(childModel, conn);
                        }
                    }
                    afterRemoveChild(bizModel, tmpChild, bizModel.getChildTypes()[i], conn);
                }
            }
            // remove the root
            if (!isViewOnly())
                remove(bizModel, conn);

        } catch (Exception e1) {
            throw new DeleteRecordException(e1.getMessage());
        }
    }

    protected byte[] getBytes(ResultSet rs, String colName) throws Exception {

        byte[] output = null;
        InputStream istream = rs.getBinaryStream(colName);
        if (istream != null) {
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            int bytesRead = 0;
            byte[] tmp = new byte[8096];
            while ((bytesRead = istream.read(tmp)) != -1) {
                bout.write(tmp, 0, bytesRead);
            }
            output = bout.toByteArray();
        }
        return output;
    }

    protected void setNullableString(PreparedStatement pStmt, int index, String str) throws SQLException {

        if (str == null)
            pStmt.setNull(index, java.sql.Types.VARCHAR);
        else
            pStmt.setString(index, str);
    }

    protected void setNullableBytes(PreparedStatement pStmt, int index, byte[] bytes) throws SQLException {

        if (bytes == null)
            pStmt.setNull(index, java.sql.Types.LONGVARBINARY);
        else {
            ByteArrayInputStream bs = new ByteArrayInputStream(bytes);
            pStmt.setBinaryStream(index, bs, bytes.length);
            // pStmt.setBytes(index, bytes);
        }
    }

    protected void setNullableBigInteger(PreparedStatement pStmt, int index, BigInteger bigInteger) throws SQLException {

        if (bigInteger == null)
            pStmt.setNull(index, java.sql.Types.INTEGER);
        else
            pStmt.setInt(index, bigInteger.intValue());
    }
    
    protected void setNullableBigDecimal(PreparedStatement pStmt, int index, BigDecimal bigDecimal) throws SQLException {

        if (bigDecimal == null)
            pStmt.setNull(index, java.sql.Types.DECIMAL);
        else
            pStmt.setBigDecimal(index, bigDecimal);
    }

    protected void setNullableDate(PreparedStatement pStmt, int index, java.sql.Date date) throws SQLException {

        if (date == null)
            pStmt.setNull(index, java.sql.Types.DATE);
        else
            pStmt.setDate(index, date);
    }

    protected void setNullableTimestamp(PreparedStatement pStmt, int index, Timestamp timestamp) throws SQLException {

        if (timestamp == null)
            pStmt.setNull(index, java.sql.Types.TIMESTAMP);
        else
            pStmt.setTimestamp(index, timestamp);
    }

    protected void setNullableAsciiStream(PreparedStatement pStmt, int index, String str) throws SQLException {

        if (str == null)
            setNullableBytes(pStmt, index, null);
        else
            pStmt.setAsciiStream(index, new java.io.ByteArrayInputStream(str.getBytes()), str.getBytes().length);
    }

    protected String getAsciiStream(InputStream istream) {

        String output = "";
        if (istream != null) {
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            try {
                int bytesRead = 0;
                byte[] tmp = new byte[8096];
                while ((bytesRead = istream.read(tmp)) != -1) {
                    bout.write(tmp, 0, bytesRead);
                }
                output = bout.toString();
                bout.close();
            } catch (IOException e) {
                Logger.logExceptions(this, e);
            }
        }

        return output;
    }

    protected String appendWildcard(String str, int length) {

        String srchStr = null;
        if (str == null)
            srchStr = "%";
        else {
            srchStr = "%" + str.trim().concat("%");
            if (srchStr.length() > length)
                srchStr = str;
        }
        return srchStr;
    }

    public static String trimCharVal(String str) {

        if (str == null) {
            return null;
        } else {
            return str.trim();
        }
    }

    public static String toCharVal(String str, int length) {

        if (str == null) {
            return null;
        } else {
            for (int i = str.length(); i < length; i++) {
                str = str.concat(" ");
            }
            return str;
        }
    }

    public String updateDateStr(java.sql.Timestamp updateDate) {

        String str = "";

        if (updateDate == null) {
            str = "UPDATE_DATE is null";
        } else {
            str = "UPDATE_DATE = ?";
        }

        return str;
    }

    public boolean checkIfFormExists(SimpleBasePk pk, Connection conn) throws FindRecordException {

        return true;
    }

    protected String getDataAccessSecurityWhereClause(SimpleBaseSrchCriteria criteria) {
        StringBuffer sb = new StringBuffer();
        Collection userDataAccessInfo = criteria.getUserSecurityList();

        if (userDataAccessInfo == null) {
            sb.append("''");
            return sb.toString();
        }

        Iterator it = userDataAccessInfo.iterator();
        HashMap map = null;
        String dept = "";

        while (it.hasNext()) {
            map = (HashMap) it.next();
            dept = (String) map.get("DEPT");

            if (map == null)
                continue;

            if (sb.length() > 0)
                sb.append(", ");

            sb.append("'");
            sb.append(dept == null ? "" : dept);
            sb.append("'");
        }

        if (sb.length() == 0)
            sb.append("''");
        return sb.toString();
    }

    protected String getDataAccessSecurityWhereClause(SimpleBaseSrchCriteria criteria, int level) {
        return getDataAccessSecurityWhereClause(criteria);
    }

    protected String getDataApprovalListWhereClause(SimpleBaseSrchCriteria criteria, int level, String docType) {

        ArrayList userApprovalList = criteria.getUserApprovalList();
        String originator = criteria.getSecurityOriginator();
        StringBuffer whereClause = new StringBuffer();
        String tmp = "";
        ArrayList statusList = new ArrayList();

        for (int i = 0; (userApprovalList != null) && i < userApprovalList.size(); i++) {
            Hashtable hash = (Hashtable) userApprovalList.get(i);
            String taskDoc = (String) hash.get("TASK_DOC");
            String status = (String) hash.get("STATUS");
            String isActive = (String) hash.get("IS_ACTIVE");
            if (docType.equalsIgnoreCase(taskDoc) && (ProjectConstants.STATUS_ALL_STATUS.equals(status))) {
                return "";
            }
        }

        for (int i = 0; (userApprovalList != null) && i < userApprovalList.size(); i++) {
            Hashtable hash = (Hashtable) userApprovalList.get(i);
            String taskDoc = (String) hash.get("TASK_DOC");
            String status = (String) hash.get("STATUS");
            String isActive = (String) hash.get("IS_ACTIVE");
            if (docType.equalsIgnoreCase(taskDoc) && (ProjectConstants.GENERIC_YES.equals(isActive))) {
                tmp = tmp + "'" + status + "',";
            }
        }

        if (tmp.length() > 0) {
            whereClause.append(" status in (");
            whereClause.append(tmp.substring(0, tmp.length() - 1));
            whereClause.append(")");
        }

        return whereClause.toString();
    }

    protected void beforeRemoveChild(SimpleBaseVo bizModel, ChildList tmpChild, Class childType, Connection conn)
            throws Exception {
    }

    protected void afterRemoveChild(SimpleBaseVo bizModel, ChildList tmpChild, Class childType, Connection conn) throws Exception {
    }

    protected void logSqlQuery(String query) {
        Logger.sql(this, "logSqlQuery(), query=" + query);
    }

    // to specify that the calling DAO is View-Only and so the dbImage should
    // not be retrieved
    // this is intended for performance optimization, take this out if problems
    // arise
    protected boolean isViewOnly() {
        return false;
    }

    /**
     * Returns the original model if !rs.next(). Else returns this.getModelForSelectByPrimaryKey(rs).
     * i.e. If the record is not found, the retrun value of this method == model.
     * 
     * @param model
     * @param pk
     * @param conn
     * @return
     * @throws FindRecordException
     */
    public SimpleBaseVo selectByPrimaryKey(SimpleBaseVo model, SimpleBasePk pk, Connection conn) throws FindRecordException { 
	    PreparedStatement pStmt = null;
	    ResultSet rs = null;
	
	    try {
	    	String query = sqlSelectByPrimaryKey();
            logSqlQuery(query);
	        pStmt = conn.prepareStatement(query);
	        setValuesForSelectByPrimaryKey(pStmt, pk);
	
	        rs = pStmt.executeQuery();
	
	        if (rs.next()) {
	            model = (SimpleBaseVo) getModelForSelectByPrimarKey(rs);
	        }
	    } catch (Exception sqe) {
	    	Logger.logExceptions(this, sqe);
	        throw new FindRecordException(sqe.getMessage());
	    } finally {
	        try {
	            if (rs != null)
	                rs.close();
	            if (pStmt != null)
	                pStmt.close();
	        } catch (SQLException sqe) {
	            Logger.logExceptions(this, sqe);
	        }
	    }
	    return model;
    }
    
    protected String sqlSelectByPrimaryKey(){
    	return null;
    }

    protected void setValuesForSelectByPrimaryKey(PreparedStatement pStmt, SimpleBasePk obj) throws SQLException{
    	
    }
    
    protected SimpleBaseVo getModelForSelectByPrimarKey(ResultSet rs) throws SQLException {
        return null;
    }
    
    public HashMap selectByCriteria(SimpleBaseVo model, SimpleBaseSrchCriteria criteria, Connection conn) throws FindRecordException {
		HashMap retHash = new HashMap();
		Statement stmt = null;
        ResultSet rs = null;
        SimpleBasePk pk = null;
        
        try {
            String query = sqlSelectByCriteria(criteria);
            logSqlQuery(query);
            stmt = conn.createStatement();
            setMaxRows(stmt);
            rs = stmt.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                    model = getModelForSelectByCriteria(rs);
                    model.setParent(null);
                    model.setIsExisting(true);
                    model.setIsDirty(true);
                    pk = getPkForSelectByCriteria(rs);
                    retHash.put(pk, model);
                }
            }

        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }

		return retHash;
	}
    
    protected String sqlSelectByCriteria(SimpleBaseSrchCriteria criteria) {
        return "";
    }
    
    protected SimpleBaseVo getModelForSelectByCriteria(ResultSet rs) throws SQLException {
        return null;
    }

    protected SimpleBasePk getPkForSelectByCriteria(ResultSet rs) throws SQLException {
        return null;
    }
    
    
}